﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using HKEA.CMS.Core.Models;
using HKEA.CMS.Core.IDAL;
using System.Data.SqlClient;
using iMaker.Library;
using HKEA.CMS.Core.DALFactory;
using System.Configuration;
using System.Data;
using HKEA.CMS.Core.Utility.Resource;
using System.Web.Configuration;

namespace HKEA.CMS.Core.DAL
{
    public class Search_Cust_InfoDAL:ISearch_Cust_Info
    {
        public static string SqlStr;
        public ExtensionModel Ext;
        public static readonly string strConn = WebConfigurationManager.AppSettings["SearchStrConnection"].ToString();
        SqlConnection conn = new SqlConnection(strConn);
        public Search_Cust_InfoDAL()
        {

        }

        #region 查询方案
        /// <summary>
        /// 方案1SQL查询,未实现参数化
        /// </summary>
        /// <param name="ent"></param>
        /// <returns></returns>
        public List<Search_Result> GetListByENT(Search_Cust_Info ent)
        {
            var resule = new List<Search_Result>();

            SqlStr=SearchResuleMethod.SearchString(ent);
            
            if (conn.State != ConnectionState.Open) { conn.Open(); }

            SqlCommand cmd = new SqlCommand(SqlStr, conn);
            cmd.CommandType = CommandType.Text;
            DataSet ds = new DataSet();
            SqlDataAdapter adp = new SqlDataAdapter();
            adp.SelectCommand = cmd;
            cmd.CommandTimeout = 360;
            if (adp != null)
            {
                adp.Fill(ds);
            }
            DataTable orders = ds.Tables[0];

            IList<Search_Result> IList = new List<Search_Result>();
            for (int i = 0; i < orders.Rows.Count; i++)
            {
                Search_Result tep = new Search_Result();
                tep.CustomerUID=orders.Rows[i][0].ToString();
                tep.CustomerID = orders.Rows[i][1].ToString();
                tep.CustType_Chi = orders.Rows[i][2].ToString();
                tep.Name_Eng = orders.Rows[i][3].ToString();
                tep.Name_Chi = orders.Rows[i][4].ToString();
                tep.IsMember = orders.Rows[i][5].ToString();
                tep.IsGCMember = orders.Rows[i][6].ToString();
                tep.IsVIP = orders.Rows[i][7].ToString();
                tep.BranchName_Chi = orders.Rows[i][8].ToString();
                tep.Industry_Eng = orders.Rows[i][9].ToString();
                tep.FirstName_Chi = orders.Rows[i][10].ToString();
                tep.LastName_Chi = orders.Rows[i][11].ToString();
                tep.FirstName_Eng = orders.Rows[i][12].ToString();
                tep.LastName_Eng = orders.Rows[i][13].ToString();
                tep.ChristianName = orders.Rows[i][14].ToString();
                tep.Position = orders.Rows[i][15].ToString();
                tep.Flat_Bldg_Eng = orders.Rows[i][16].ToString();
                tep.Flat_Bldg_Chi = orders.Rows[i][17].ToString();
                tep.Street_Rd_Eng = orders.Rows[i][18].ToString();
                tep.Area_Eng = orders.Rows[i][19].ToString();
                tep.District_Eng = orders.Rows[i][20].ToString();
                tep.City_Eng = orders.Rows[i][21].ToString();
                tep.Country_Eng = orders.Rows[i][22].ToString();
                tep.Tel1 = orders.Rows[i][23].ToString();
                tep.Tel2 = orders.Rows[i][24].ToString();
                tep.Fax1 = orders.Rows[i][25].ToString();
                tep.Fax2 = orders.Rows[i][26].ToString();
                tep.Email1 = orders.Rows[i][27].ToString();
                IList.Add(tep);
            }
            resule = IList.ToList();
            return resule;
        }
        #endregion

        #region 参数化SQL
        /// <summary>
        /// 方案二SQL查询,实现参数化
        /// </summary>
        /// <param name="ent"></param>
        /// <returns></returns>
        public List<Search_Result> GetListParmByENT(Search_Cust_Info ent)
        {

            var resule = new List<Search_Result>();
            SqlStr = SearchResuleMethod.SqlParamString(ent);
            try
            {
                if (conn.State != ConnectionState.Open) { conn.Open(); }

                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = SqlStr;
                #region SQL参数
                if (!string.IsNullOrEmpty(ent.CustomerName))
                {
                    cmd.Parameters.Add("@CustomerName", SqlDbType.NVarChar, 600).Value = Len(ent.CustomerName);
                }
                if (!string.IsNullOrEmpty(ent.CustomerID))
                {
                    cmd.Parameters.Add("@CustomerID", SqlDbType.NVarChar, 255).Value = ent.CustomerID;
                }
                if (!string.IsNullOrEmpty(ent.CustomerType))
                {
                    cmd.Parameters.Add("@CustomerType", SqlDbType.Int, 100).Value = Convert.ToInt32(ent.CustomerType);
                }
                if (!string.IsNullOrEmpty(ent.MemberShipSince))
                {
                    int Year = Convert.ToDateTime(ent.MemberShipSince).Year;
                    int Moth = Convert.ToDateTime(ent.MemberShipSince).Month;
                    cmd.Parameters.Add("@YearEntry", SqlDbType.NVarChar, 255).Value = Year;
                    cmd.Parameters.Add("@MothEntry", SqlDbType.NVarChar, 255).Value = Moth;

                }
                if (!string.IsNullOrEmpty(ent.MemberShipStatus))
                {
                    cmd.Parameters.Add("@MemberShipStatus", SqlDbType.NVarChar, 255).Value = ent.MemberShipStatus;
                }
                if (!string.IsNullOrEmpty(ent.BrandName))
                {
                    cmd.Parameters.Add("@BrandName", SqlDbType.NVarChar, 255).Value = Len(ent.BrandName);
                }
                if (!string.IsNullOrEmpty(ent.CustomerIntroduction))
                {
                    cmd.Parameters.Add("@CustomerIntroduction", SqlDbType.NVarChar, 600).Value = Len(ent.CustomerIntroduction);
                }
                if (!string.IsNullOrEmpty(ent.NoOfEmployee))
                {
                    cmd.Parameters.Add("@NoOfEmployee", SqlDbType.NVarChar, 255).Value = ent.NoOfEmployee;
                }
                if (!string.IsNullOrEmpty(ent.YearEstablished))
                {
                    if (ent.YearEstablished.Length == 4)
                    {
                        cmd.Parameters.Add("@YearEstablished", SqlDbType.NVarChar, 255).Value = ent.YearEstablished;
                    }
                    else if (ent.YearEstablished.Length > 4 & ent.YearEstablished.Length < 8)
                    {
                        int Year = Convert.ToDateTime(ent.YearEstablished).Year;
                        int Moth = Convert.ToDateTime(ent.YearEstablished).Month;
                        cmd.Parameters.Add("@YearEst", SqlDbType.NVarChar, 255).Value = Year;
                        cmd.Parameters.Add("@MothEst", SqlDbType.NVarChar, 255).Value = Moth;
                    }
                    else if (ent.YearEstablished.Length > 8)
                    {
                        cmd.Parameters.Add("@YearEstablished", SqlDbType.NVarChar, 255).Value = Convert.ToDateTime(ent.MemberShipSince);
                    }
                }
                if (!string.IsNullOrEmpty(ent.BR))
                {
                    cmd.Parameters.Add("@BR", SqlDbType.NVarChar, 255).Value = Len(ent.BR);
                }
                if (!string.IsNullOrEmpty(ent.HKIDPassportNo))
                {
                    cmd.Parameters.Add("@HKIDPassportNo", SqlDbType.NVarChar, 255).Value = ent.HKIDPassportNo;
                }
                if (!string.IsNullOrEmpty(ent.GC))
                {
                    cmd.Parameters.Add("@GC", SqlDbType.Int, 10).Value = Convert.ToInt32(ent.GC);
                }
                if (!string.IsNullOrEmpty(ent.VIP))
                {
                    cmd.Parameters.Add("@VIP", SqlDbType.NVarChar, 10).Value = Convert.ToInt32(ent.VIP);
                }
                if (!string.IsNullOrEmpty(ent.HQLocatedInHk))
                {
                    cmd.Parameters.Add("@HQLocatedInHk", SqlDbType.Int, 10).Value = Convert.ToInt32(ent.HQLocatedInHk);
                }
                if (!string.IsNullOrEmpty(ent.Address))
                {
                    cmd.Parameters.Add("@Address", SqlDbType.NVarChar, 500).Value = ent.Address;
                }
                if (!string.IsNullOrEmpty(ent.Country))
                {
                    cmd.Parameters.Add("@Country", SqlDbType.NVarChar, 255).Value = ent.Country;
                }
                if (!string.IsNullOrEmpty(ent.Continent))
                {
                    cmd.Parameters.Add("@Continent", SqlDbType.NVarChar, 255).Value = ent.Continent;
                }
                if (!string.IsNullOrEmpty(ent.Position))
                {
                    cmd.Parameters.Add("@Position", SqlDbType.NVarChar, 255).Value = Len(ent.Position);
                }
                if (!string.IsNullOrEmpty(ent.Email))
                {
                    cmd.Parameters.Add("@Email", SqlDbType.NVarChar, 255).Value = ent.Email;
                }
                if (!string.IsNullOrEmpty(ent.EmailUnsubscribe))
                {
                    cmd.Parameters.Add("@EmailUnsubscribe", SqlDbType.Int, 10).Value = Convert.ToInt32(ent.EmailUnsubscribe);
                }
                if (!string.IsNullOrEmpty(ent.Fax))
                {
                    cmd.Parameters.Add("@Fax", SqlDbType.NVarChar, 255).Value = ent.Fax;
                }
                if (!string.IsNullOrEmpty(ent.FaxUnsubscribe))
                {
                    cmd.Parameters.Add("@FaxUnsubscribe", SqlDbType.Int, 10).Value = Convert.ToInt32(ent.FaxUnsubscribe);
                }
                if (!string.IsNullOrEmpty(ent.Fax_IsDNC))
                {
                    cmd.Parameters.Add("@Fax_IsDNC", SqlDbType.Int, 10).Value = Convert.ToInt32(ent.Fax_IsDNC);
                }
                if (!string.IsNullOrEmpty(ent.Tel))
                {
                    cmd.Parameters.Add("@Tel", SqlDbType.NVarChar, 255).Value = ent.Tel;
                }
                if (!string.IsNullOrEmpty(ent.Tel_IsSubscribe))
                {
                    cmd.Parameters.Add("@Tel_IsSubscribe", SqlDbType.Int, 10).Value = Convert.ToInt32(ent.Tel_IsSubscribe);
                }
                if (!string.IsNullOrEmpty(ent.Tel_IsDNC))
                {
                    cmd.Parameters.Add("@Tel_IsDNC", SqlDbType.Int, 10).Value = Convert.ToInt32(ent.Tel_IsDNC);
                }
                if (!string.IsNullOrEmpty(ent.ContactPersonName))
                {
                    cmd.Parameters.Add("@ContactPersonName", SqlDbType.NVarChar, 255).Value = ent.ContactPersonName;
                }
                if (!string.IsNullOrEmpty(ent.Salutation))
                {
                    cmd.Parameters.Add("@Salutation", SqlDbType.NVarChar, 255).Value = Che(ent.Salutation);
                }
                if (!string.IsNullOrEmpty(ent.CommitteeType))
                {
                    cmd.Parameters.Add("@CommitteeType", SqlDbType.NVarChar, 255).Value = ent.CommitteeType;
                }
                if (!string.IsNullOrEmpty(ent.CommitteePosition))
                {
                    cmd.Parameters.Add("@CommitteePosition", SqlDbType.NVarChar, 255).Value = Che(ent.CommitteePosition);
                }
                if (!string.IsNullOrEmpty(ent.YearTerm))
                {
                    cmd.Parameters.Add("@YearTerm", SqlDbType.NVarChar, 255).Value = Len(ent.YearTerm);
                }
                //if (!string.IsNullOrEmpty(ent.Attribute))
                //{
                //    cmd.Parameters.Add("@Attribute", SqlDbType.NVarChar).Value = ent.Attribute;
                //}
                //if (!string.IsNullOrEmpty(ent.JobNaturexdcc))
                //{
                //    cmd.Parameters.Add("@JobNaturexdcc", SqlDbType.NVarChar).Value = ent.JobNaturexdcc;
                //}
                if (!string.IsNullOrEmpty(ent.ProductSpecifictions))
                {
                    cmd.Parameters.Add("@ProductSpecifictions", SqlDbType.NVarChar, 255).Value = Len(ent.ProductSpecifictions);
                }
                if (!string.IsNullOrEmpty(ent.BranchAddress))
                {
                    cmd.Parameters.Add("@BranchAddress", SqlDbType.NVarChar, 255).Value = Len(ent.BranchAddress);
                }
                if (!string.IsNullOrEmpty(ent.BranchName))
                {
                    cmd.Parameters.Add("@BranchName", SqlDbType.NVarChar, 255).Value = Len(ent.BranchName);
                }
                if (!string.IsNullOrEmpty(ent.BusinessNatur))
                {
                    cmd.Parameters.Add("@BusNum", SqlDbType.Int, 255).Value = ent.BusNum;
                }
                if (!string.IsNullOrEmpty(ent.ProductCategory))
                {
                    cmd.Parameters.Add("@ProNum", SqlDbType.Int, 255).Value = ent.ProNum;
                }
                if (!string.IsNullOrEmpty(ent.MainMarket))
                {
                    cmd.Parameters.Add("@MarketNum", SqlDbType.Int, 255).Value = ent.MarketNum;
                }
                #endregion

                DataSet ds = new DataSet();
                SqlDataAdapter adp = new SqlDataAdapter();
                adp.SelectCommand = cmd;
                cmd.CommandTimeout = 360;
                if (adp != null)
                {
                    adp.Fill(ds);
                }
                DataTable orders = ds.Tables[0];

                IList<Search_Result> IList = new List<Search_Result>();
                for (int i = 0; i < orders.Rows.Count; i++)
                {
                    Search_Result tep = new Search_Result();
                    tep.CustomerUID = orders.Rows[i][0].ToString();
                    tep.CustomerID = orders.Rows[i][1].ToString();
                    tep.CustType_Chi = orders.Rows[i][2].ToString();
                    tep.Name_Eng = orders.Rows[i][3].ToString();
                    tep.Name_Chi = orders.Rows[i][4].ToString();
                    tep.IsMember = orders.Rows[i][5].ToString();
                    tep.IsGCMember = orders.Rows[i][6].ToString();
                    tep.IsVIP = orders.Rows[i][7].ToString();
                    tep.BranchName_Chi = orders.Rows[i][8].ToString();
                    tep.Industry_Eng = orders.Rows[i][9].ToString();
                    tep.FirstName_Chi = orders.Rows[i][10].ToString();
                    tep.LastName_Chi = orders.Rows[i][11].ToString();
                    tep.FirstName_Eng = orders.Rows[i][12].ToString();
                    tep.LastName_Eng = orders.Rows[i][13].ToString();
                    tep.ChristianName = orders.Rows[i][14].ToString();
                    tep.Position = orders.Rows[i][15].ToString();
                    tep.Flat_Bldg_Eng = orders.Rows[i][16].ToString();
                    tep.Flat_Bldg_Chi = orders.Rows[i][17].ToString();
                    tep.Street_Rd_Eng = orders.Rows[i][18].ToString();
                    tep.Area_Eng = orders.Rows[i][19].ToString();
                    tep.District_Eng = orders.Rows[i][20].ToString();
                    tep.City_Eng = orders.Rows[i][21].ToString();
                    tep.Country_Eng = orders.Rows[i][22].ToString();
                    tep.Tel1 = orders.Rows[i][23].ToString();
                    tep.Tel2 = orders.Rows[i][24].ToString();
                    tep.Fax1 = orders.Rows[i][25].ToString();
                    tep.Fax2 = orders.Rows[i][26].ToString();
                    tep.Email1 = orders.Rows[i][27].ToString();
                    IList.Add(tep);
                }
                resule = IList.ToList();

            }
            catch
            {
            }
            return resule;
        }
        #endregion

        
        string Len (string str) {
            string resule = "";
            resule="\""+str+"\" or \""+str+"*\"";
            return resule;
        }
        string Che(string str) {
            string resule = "";
            resule = "\"" + str + "\"";
            return resule;
        }
                
    }
}
